﻿// ================================================================================
//        File: DbBase.cs
//        Desc: 数据库操作助手类
//        Auth: JF
//        Date: 
// ================================================================================
//        Change History
// ================================================================================
//        Date:         Author:                  Description:
//        --------     --------              -------------------
//       2011/09/22     Alex                    注释掉Open(),改为创建connection时打开，结束命令时关闭
// ================================================================================


using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using EShop.Common;

namespace EShop.DAL
{
    public abstract class DbBase
    {
        protected string _provideName;
        protected DbConnection _connection = null;
        protected DbTransaction _transaction = null;

        protected abstract DbConnection CreateConnection();
        private static Dictionary<string, DbProviderFactory> _dbProviderFactory = new Dictionary<string, DbProviderFactory>();

        ///// <summary>打开数据库
        ///// -- 改为SQL CreateConnection()打开
        ///// </summary>
        //public void Open()
        //{
        //    _connection = CreateConnection();
        //    try
        //    {
        //        if (_connection != null && _connection.State.Equals(ConnectionState.Closed))
        //            _connection.Open();
        //    }
        //    catch (Exception e)
        //    {
        //        throw e;
        //    }
        //}

        /// <summary>关闭数据库
        /// 
        /// </summary>
        public void Close()
        {
            try
            {
                if (_connection != null && _connection.State == ConnectionState.Open)
                    _connection.Close();
            }
            catch (Exception e)
            {
                log.Write(e.Message);
                throw e; 
            }
        }

        private DbProviderFactory GetDataFactory()
        {
            if (!_dbProviderFactory.ContainsKey(_provideName))
            {
                DbProviderFactory dataFactory = DbProviderFactories.GetFactory(_provideName);
                _dbProviderFactory.Add(_provideName, dataFactory);
            }
            return _dbProviderFactory[_provideName];
        }

        /// <summary>执行一条无数据返回的SQL语句
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <returns>如果返回结果为0或小于0，表示执行失败</returns>
        public int ExecuteNonQuerySQL(string Sql)
        {
            return ExecuteNonQuerySQL(Sql, CommandType.Text);
        }
        /// <summary>执行一条无数据返回的SQL语句(存储过程)
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <returns>如果返回结果为0或小于0，表示执行失败</returns>
        public int ExecuteNonQuerySQL(string Sql, CommandType commandType)
        {
            return ExecuteNonQuerySQL(Sql, commandType, null);
        }
        /// <summary>执行一条无数据返回的SQL语句(存储过程)
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="Parameter">参数</param>
        /// <returns>如果返回结果为0或小于0，表示执行失败</returns>
        public int ExecuteNonQuerySQL(string Sql, CommandType commandType, params DbParameter[] Parameter)
        {
            int reInt = 0;
            try
            {
                using (DbCommand Cmd = CreateConnection().CreateCommand())
                {
                    Cmd.CommandText = Sql;
                    SetCommandParameters(Cmd, Parameter);
                    Cmd.CommandType = commandType;
                    if (_transaction != null)
                    {
                        Cmd.Transaction = _transaction;
                    }
                    reInt = Cmd.ExecuteNonQuery();
                    Cmd.Parameters.Clear();
                }
                return reInt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                this.Close();
            }
        }
        
        /// <summary>执行一条聚合查询(返回唯一结果)的SQL语句
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <returns>返回唯一的Objcet类型的数据</returns>
        public object ExecuteScalarSQL(string Sql)
        {
            return ExecuteScalarSQL(Sql, CommandType.Text);
        }
        /// <summary>执行一条聚合查询(返回唯一结果)的SQL语句(存储过程)
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <returns>返回唯一的Objcet类型的数据</returns>
        public object ExecuteScalarSQL(string Sql, CommandType commandType)
        {
            return ExecuteScalarSQL(Sql, commandType, null);
        }
        /// <summary>执行一条聚合查询(返回唯一结果)的SQL语句(存储过程)
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="Parameter">参数</param>
        /// <returns>返回唯一的Objcet类型的数据</returns>
        public object ExecuteScalarSQL(string Sql, CommandType commandType, params DbParameter[] Parameter)
        {
            object reObject = null;
            try
            {
                using (DbCommand Cmd = CreateConnection().CreateCommand())
                {
                    Cmd.CommandText = Sql;
                    SetCommandParameters(Cmd, Parameter);
                    Cmd.CommandType = commandType;
                    if (_transaction != null)
                        Cmd.Transaction = _transaction;
                    reObject = Cmd.ExecuteScalar();
                    Cmd.Parameters.Clear();
                }
                return reObject;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                this.Close();
            }
            
        }

        /// <summary>执行一条SQL语句，返回DataReader
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <returns>返回一个DataReader</returns>
        public DbDataReader ExecuteReaderSQL(string Sql)
        {
            return ExecuteReaderSQL(Sql, CommandType.Text);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataReader
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <returns>返回一个DataReader</returns>
        public DbDataReader ExecuteReaderSQL(string Sql, CommandType commandType)
        {
            return ExecuteReaderSQL(Sql, commandType, null);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataReader
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="Parameter">参数</param>
        /// <returns>返回一个DataReader</returns>
        public DbDataReader ExecuteReaderSQL(string Sql, CommandType commandType, params DbParameter[] Parameter)
        {
            DbDataReader dbDataReader = null;
            using (DbCommand Cmd = CreateConnection().CreateCommand())
            {
                Cmd.CommandText = Sql;
                SetCommandParameters(Cmd, Parameter);
                Cmd.CommandType = commandType;
                if (_transaction != null) Cmd.Transaction = _transaction;
                dbDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Cmd.Parameters.Clear();
            }

            return dbDataReader;
        }

        /// <summary>执行一条SQL语句，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql)
        {
            return ExecuteDataSetSQL(Sql, CommandType.Text);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql, CommandType commandType)
        {
            return ExecuteDataSetSQL(Sql, commandType, null);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="Parameter">参数</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql, CommandType commandType, params DbParameter[] Parameter)
        {
            try
            {
                DataSet ds = null;
                using (DbCommand Cmd = CreateConnection().CreateCommand())
                {
                    DbDataAdapter da = GetDataFactory().CreateDataAdapter();
                    Cmd.CommandText = Sql;
                    SetCommandParameters(Cmd, Parameter);
                    Cmd.CommandType = commandType;
                    if (_transaction != null) Cmd.Transaction = _transaction;
                    da.SelectCommand = Cmd;
                    ds = new DataSet();
                    da.Fill(ds);
                    Cmd.Parameters.Clear();
                }

                return ds;
            }
            catch (Exception e)
            {
                log.Write(e.Message);
                throw;
            }
            finally
            {
                this.Close();
            }
            
        }


        /// <summary>执行一条SQL语句，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql, int iBeginIndex, int iLenght)
        {
            return ExecuteDataSetSQL(Sql,iBeginIndex,iLenght, CommandType.Text);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql, int iBeginIndex, int iLenght, CommandType commandType)
        {
            return ExecuteDataSetSQL(Sql,iBeginIndex,iLenght, commandType, null);
        }
        /// <summary>执行一条SQL语句(存储过程)，返回DataSet
        /// 
        /// </summary>
        /// <param name="Sql">SQL语句(存储过程)</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="Parameter">参数</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSetSQL(string Sql, int iBeginIndex,int iLenght,CommandType commandType, params DbParameter[] Parameter)
        {
            try
            {
                DataSet ds = null;
                using (DbCommand Cmd = CreateConnection().CreateCommand())
                {
                    DbDataAdapter da = GetDataFactory().CreateDataAdapter();
                    Cmd.CommandText = Sql;
                    SetCommandParameters(Cmd, Parameter);
                    Cmd.CommandType = commandType;
                    if (_transaction != null) Cmd.Transaction = _transaction;
                    da.SelectCommand = Cmd;
                    ds = new DataSet();
                    da.Fill(ds, iBeginIndex, iLenght, "tempTable");
                    Cmd.Parameters.Clear();
                }
                return ds;
            }
            catch (Exception)
            {
                
                throw;
            }
            finally
            {
                this.Close();
            }
            
        }

        private void SetCommandParameters(DbCommand cmd, DbParameter[] Parameter)
        {
            if (Parameter != null)
            {
                foreach (DbParameter parameter in Parameter)
                    cmd.Parameters.Add(parameter);
            }
        }

    }
}